import json
import gzip
from pathlib import Path
from datetime import datetime
import hashlib

#
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.engine.interfaces import Dialect


class CompressedJSON(TypeDecorator):
    """
    自动压缩的JSON类型
    """

    impl = LargeBinary
    cache_ok = True

    def process_bind_param(self, value: Any, dialect: Dialect):
        """
        将Python对象转换为压缩的二进制数据存储到数据库
        :param value: 要存储的JSON数据
        :param dialect: 数据库方言
        :return: 压缩后的二进制数据
        """
        if value is not None:
            json_str = json.dumps(value, ensure_ascii=False)
            return gzip.compress(json_str.encode("utf-8"))
        return None

    def process_result_value(self, value, dialect: Dialect):
        """
        将数据库中的压缩二进制数据解压并转换为Python对象
        :param value: 数据库中的压缩数据
        :param dialect: 数据库方言
        :return: 解压后的JSON对象
        """
        if value is not None:
            json_str = gzip.decompress(value).decode("utf-8")
            return json.loads(json_str)
        return None


Base = declarative_base()


def _str_to_md5(input_str: str):
    md5_hash = hashlib.md5()
    md5_hash.update(input_str.encode("utf-8"))
    md5_hex = md5_hash.hexdigest()
    return md5_hex


class TableGit(Base):
    __tablename__ = "gitReleases"
    __table_args__ = (Index("idx_host_project", "host", "project", unique=True),)

    id = Column(Integer, primary_key=True, autoincrement=True)
    host = Column(String(64), nullable=False)
    project = Column(String(255), nullable=False)
    lastPullTime = Column(DateTime, nullable=False)
    response = Column(CompressedJSON, nullable=True)
    lastestReleaseTime = Column(DateTime, nullable=True)

    def to_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


class TableDLHeader(Base):
    __tablename__ = "DLHeader"

    id = Column(Integer, primary_key=True, autoincrement=True)
    url = Column(String, nullable=False)
    urlHash = Column(String(32), nullable=False, index=True, unique=True)
    lastPullTime = Column(DateTime, nullable=False)
    contentLength = Column(BigInteger, nullable=False)
    etag = Column(String(255), nullable=True)
    lastestReleaseTime = Column(DateTime, nullable=False)
    urlRedirected = Column(String, nullable=True)
    attachmentName = Column(String, nullable=True)

    def to_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


class TableMirrorFTPInfo(Base):
    __tablename__ = "mirrorFTPInfo"

    id = Column(Integer, primary_key=True, autoincrement=True)
    entryUrl = Column(String, nullable=False)
    urlWithPattern = Column(String, nullable=False)
    urlHash = Column(String(32), nullable=False, index=True, unique=True)
    lastPullTime = Column(DateTime, nullable=False)
    response = Column(CompressedJSON, nullable=True)
    lastestReleaseTime = Column(DateTime, nullable=True)

    def to_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


class CachedbConn:
    def __init__(self, db_path: Path):
        self.engine = create_engine("sqlite:///{}".format(db_path), echo=False)
        Base.metadata.create_all(self.engine)
        with self.engine.connect() as connection:
            # connection.connection是原始的sqlite3connection
            connection.connection.execute("VACUUM")
        self.SessionFactory = sessionmaker(bind=self.engine)

    def close(self):
        if self.engine is not None:
            self.engine.dispose()
        self.engine = None

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()

    def put_git(self, host: str, project: str, response: dict, lastestReleaseTime: datetime):
        with self.SessionFactory() as session:
            record = session.query(TableGit).filter_by(host=host, project=project).first()
            if record is None:
                putting_data = TableGit(
                    host=host,
                    project=project,
                    lastPullTime=datetime.now(),
                    response=response,
                    lastestReleaseTime=lastestReleaseTime,
                )
                session.add(putting_data)
            else:
                record.response = response
                record.lastestReleaseTime = lastestReleaseTime
                record.lastPullTime = datetime.now()
            session.commit()

    def get_git(self, host: str, project: str):
        with self.SessionFactory() as session:
            result = session.query(TableGit).filter_by(host=host, project=project).first()
            if result is not None:
                return result.to_dict()
            return None

    def put_DLheader(
        self,
        url: str,
        contentLength: int,
        etag: str,
        lastestReleaseTime: datetime,
        urlRedirected: str,
        attachmentName: str,
    ):
        with self.SessionFactory() as session:
            urlHash = _str_to_md5(url)
            record = session.query(TableDLHeader).filter_by(urlHash=urlHash).first()
            if record is None:
                putting_data = TableDLHeader(
                    url=url,
                    urlHash=urlHash,
                    lastPullTime=datetime.now(),
                    contentLength=contentLength,
                    etag=etag,
                    lastestReleaseTime=lastestReleaseTime,
                    urlRedirected=urlRedirected,
                    attachmentName=attachmentName,
                )
                session.add(putting_data)
            else:
                record.contentLength = contentLength
                record.etag = etag
                record.lastestReleaseTime = lastestReleaseTime
                record.urlRedirected = urlRedirected
                record.attachmentName = attachmentName
                record.lastPullTime = datetime.now()
            session.commit()

    def get_DLheader(self, url: str):
        with self.SessionFactory() as session:
            result = session.query(TableDLHeader).filter_by(urlHash=_str_to_md5(url)).first()
            if result is not None:
                return result.to_dict()
            return None

    def put_MirrorFTP_info(
        self,
        entryUrl: str,
        urlWithPattern: str,
        response: list,
        lastestReleaseTime: datetime,
    ):
        with self.SessionFactory() as session:
            urlHash = _str_to_md5(urlWithPattern)
            record = session.query(TableMirrorFTPInfo).filter_by(urlHash=urlHash).first()
            if record is None:
                putting_data = TableMirrorFTPInfo(
                    entryUrl=entryUrl,
                    urlWithPattern=urlWithPattern,
                    urlHash=urlHash,
                    response=response,
                    lastPullTime=datetime.now(),
                    lastestReleaseTime=lastestReleaseTime,
                )
                session.add(putting_data)
            else:
                record.response = response
                record.lastestReleaseTime = lastestReleaseTime
                record.lastPullTime = datetime.now()
            session.commit()

    def get_MirrorFTP_info(self, urlWithPattern: str):
        with self.SessionFactory() as session:
            result = session.query(TableMirrorFTPInfo).filter_by(urlHash=_str_to_md5(urlWithPattern)).first()
            if result is not None:
                return result.to_dict()
            return None
